using System;
using System.Linq;
using NUnit.Framework;
using PropertyExpression.Common;
using PropertyExpression.ControllableQuery;
using PropertyExpression.ControllableQuery.Toolkit;

namespace ConsoleApplication2
{
    [TestFixture]
    public class QueryTest
    {
        [Test]
        public void CheckAllQueries()
        {
            var qRepository = new FindUsagesQRepository();
            QueryChecker.CheckAllQueries(
                QExecutor.ConnectionString,
                typeof (Program).Assembly,
                (type, context) => {
                    if (typeof(Program.RedSwitch).Equals(type))
                        return typeof (Program).Assembly.GetTypes().Where(type.IsAssignableFrom)
                            .SelectMany(_ => context.GetConstructorInvocations(_));
                    throw new ApplicationException();
                },
                qRepository);
            foreach (var qLocationString in qRepository.FindUsages(
                QExecutor.ConnectionString, "BillOfMaterialsLine", "PartNumber".AsOption())
                .Select(QLocationExtensions.GetQLocationString))
            {
                Console.WriteLine(qLocationString);                
            }
        }

        private class IdGenerator
        {
            private int i;

            public int NewId()
            {
                return i++;
            }
        }

        [Test]
        public void InsertTestData()
        {
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM ServicePartsPage").NonQuery());
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM ModelPublication").NonQuery());
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM LocalizedPublication").NonQuery());
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM Publication").NonQuery());
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM PublicationType").NonQuery());
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM BillOfMaterialsLine").NonQuery());
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM BillOfMaterialsHeader").NonQuery());
            QExecutor.ExecuteNonQuery(() => new Q("DELETE  FROM Model").NonQuery());

            var idGenerator = new IdGenerator();
            var modelCode = "M1";
            var modelSequence = "MS1";
            var modelId = idGenerator.NewId();
            InsertModel(modelId, modelCode, modelSequence);
            var publicationTypeID = idGenerator.NewId();
            InsertPublicationType(publicationTypeID, 1);
            var publicationID1 = idGenerator.NewId();
            InsertPublication(publicationID1, "P1", publicationTypeID, Option.Nothing<int>());
            var languageCode = "en";
            InsertLocalizedPublication("PubName1", idGenerator.NewId(), publicationID1, languageCode);
            var modelPublicationId = idGenerator.NewId();
            InsertModelPublication(modelPublicationId, modelCode, modelSequence, publicationID1);
            var billOfMaterialsHeaderId1 = idGenerator.NewId();
            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId1);
            var partNumber1 = "PN1";
            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 1, 0, 1, 1, partNumber1, billOfMaterialsHeaderId1);
            var partNumber2 = "PN2";
            InsertBillOfMaterialsLine(idGenerator.NewId(), 0, 1, 1, 1, 2, partNumber2, billOfMaterialsHeaderId1);
            var partNumber3 = "PN3";
            InsertBillOfMaterialsLine(idGenerator.NewId(), 0, 1, 1, 1, 3, partNumber3, billOfMaterialsHeaderId1);
            var servicePartsPageId1 = idGenerator.NewId();
            InsertServicePartsPage(servicePartsPageId1, modelPublicationId, billOfMaterialsHeaderId1);
            var billOfMaterialsHeaderId2 = idGenerator.NewId();
            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId2);
            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 1, 1, 0, 1, partNumber1, billOfMaterialsHeaderId2);
            InsertServicePartsPage(idGenerator.NewId(), modelPublicationId, billOfMaterialsHeaderId2);
            var billOfMaterialsHeaderId3 = idGenerator.NewId();
            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId3);
            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 0, 1, 1, 1, partNumber2, billOfMaterialsHeaderId3);
            InsertServicePartsPage(idGenerator.NewId(), modelPublicationId, billOfMaterialsHeaderId3);
            var publicationID2 = idGenerator.NewId();
            InsertPublication(publicationID2, "P2", publicationTypeID, publicationID2.AsOption());
            InsertLocalizedPublication("PubName2", idGenerator.NewId(), publicationID2, languageCode);
            var modelPublicationId2 = idGenerator.NewId();
            InsertModelPublication(modelPublicationId2, modelCode, modelSequence, publicationID2);
            var billOfMaterialsHeaderId4 = idGenerator.NewId();
            InsertBillOfMaterialsHeader(billOfMaterialsHeaderId4);
            InsertBillOfMaterialsLine(idGenerator.NewId(), 1, 0, 1, 1, 1, partNumber3, billOfMaterialsHeaderId4);
            InsertServicePartsPage(idGenerator.NewId(), modelPublicationId2, billOfMaterialsHeaderId4);

            Assert.AreEqual(
                1,
                LineWithLink(modelId, languageCode, billOfMaterialsHeaderId1, servicePartsPageId1, modelPublicationId, false, false));
            Assert.AreEqual(
                3,
                LineWithLink(modelId, languageCode, billOfMaterialsHeaderId1, servicePartsPageId1, modelPublicationId, true, false));
            Assert.AreEqual(
                2,
                LineWithLink(modelId, languageCode, billOfMaterialsHeaderId1, servicePartsPageId1, modelPublicationId, true, true));
        }

        private static int LineWithLink(int modelId, string languageCode, int billOfMaterialsHeaderId1, int servicePartsPageId1, int modelPublicationId, bool redCondition, bool blueCondition)
        {
            return QExecutor.MaterializeReader(
                context => new {
                                   billOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId1),
                                   modelPublicationId = context.Param(modelPublicationId),
                                   servicePartsPageId = context.Param(servicePartsPageId1),
                                   languageCode = context.Param(languageCode),
                                   modelId = context.Param(modelId),
                                   redCondition,
                                   blueCondition
                               },
                p => {
                    var q = new Q(@"FROM    BillOfMaterialsLine
WHERE   BillOfMaterialsHeaderId = ")._(p.billOfMaterialsHeaderId)._(@"
        AND ( ( BillOfMaterialsLine.MainLinkExcludedFrom = 0
                AND EXISTS ( SELECT 1
                             FROM   MainPartLink
                             WHERE  MainPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                                    AND MainPartLink.ModelPublicationId = ")._(p.modelPublicationId)._(@"
                                    AND MainPartLink.ServicePartsPageId <> ")._(p.servicePartsPageId)._(@" )
              )");
                    if (p.redCondition)
                    {
                        q._(@"
              OR ( BillOfMaterialsLine.InnerLinkExcludedFrom = 0
                   AND EXISTS ( SELECT  1
                                FROM    InnerPartLink
                                        INNER JOIN Model
                                            ON InnerPartLink.ModelCode = Model.ModelCode
                                               AND InnerPartLink.ModelSequence = Model.ModelSequence");
                        if (p.blueCondition)
                            q._(@"
                                        INNER JOIN Publication
                                            ON InnerPartLink.PublicationId = Publication.PublicationId");
                        q._(@"
                                        INNER JOIN LocalizedPublication
                                            ON InnerPartLink.PublicationId = LocalizedPublication.PublicationId
                                               AND LocalizedPublication.LanguageCode = ")._(p.languageCode)._(@"
                                WHERE   InnerPartLink.PartNumber = BillOfMaterialsLine.PartNumber
                                        AND Model.ModelId = ")._(p.modelId);
                        if (p.blueCondition)
                            q._(@"
                                        AND Publication.FollowingPublicationId IS NULL");
                        q._(@" )
                 )");
                    }
                    q._(@"
            )");
                    return q.Select(_ => new {
                                                 BillOfMaterialsLineId = _._<int>(),
                                                 PartNumber = _._<string>()
                                             });
                }).Count();
        }

        private static void InsertPublicationType(int publicationTypeID, int code)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   PublicationTypeId = context.Param(publicationTypeID),
                                   Code = context.Param(code)
                               },
                p => new Q(@"INSERT  INTO PublicationType
        ( PublicationTypeId,
          Code,
          Decimal1,
          Money1
        )
VALUES  ( ")._(p.PublicationTypeId)._(@",
          ")._(p.Code)._(@",
          7,
          7
        )").NonQuery());
        }

        private static void InsertPublication(int publicationID, string code, int publicationTypeId, IOption<int> followingPublicationId)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   PublicationId = context.Param(publicationID),
                                   Code = context.Param(code),
                                   PublicationTypeId = context.Param(publicationTypeId),
                                   FollowingPublicationId = context.Param(followingPublicationId)
                               },
                p => new Q(@"INSERT  INTO Publication
        ( PublicationId,
          Code,
          PublicationTypeId,
          FollowingPublicationId
        )
VALUES  ( ")._(p.PublicationId)._(@",
          ")._(p.Code)._(@",
          ")._(p.PublicationTypeId)._(@",
          ")._(p.FollowingPublicationId)._(@"
        )").NonQuery());
        }

        private static void InsertModelPublication(int modelPublicationId, string modelCode, string modelSequence, int publicationId)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   ModelPublicationId = context.Param(modelPublicationId),
                                   ModelCode = context.Param(modelCode),
                                   ModelSequence = context.Param(modelSequence),
                                   PublicationId = context.Param(publicationId)
                               },
                p => new Q(@"INSERT  INTO ModelPublication
        ( ModelPublicationId,
          ModelCode,
          ModelSequence,
          PublicationId
        )
VALUES  ( ")._(p.ModelPublicationId)._(@",
          ")._(p.ModelCode)._(@",
          ")._(p.ModelSequence)._(@",
          ")._(p.PublicationId)._(@"
        )").NonQuery());
        }

        private static void InsertServicePartsPage(int servicePartsPageId, int modelPublicationId, int billOfMaterialsHeaderId)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   ServicePartsPageId = context.Param(servicePartsPageId),
                                   ModelPublicationId = context.Param(modelPublicationId),
                                   BillOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId)
                               },
                p => new Q(@"INSERT  INTO ServicePartsPage
        ( ServicePartsPageId,
          ModelPublicationId,
          BillOfMaterialsHeaderId
        )
VALUES  ( ")._(p.ServicePartsPageId)._(@",
          ")._(p.ModelPublicationId)._(@",
          ")._(p.BillOfMaterialsHeaderId)._(@"
        )").NonQuery());
        }

        private static void InsertBillOfMaterialsHeader(int billOfMaterialsHeaderId)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   BillOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId)
                               },
                p => new Q(@"INSERT  INTO BillOfMaterialsHeader
        ( BillOfMaterialsHeaderId
        )
VALUES  ( ")._(p.BillOfMaterialsHeaderId)._(@"
        )").NonQuery());
        }

        private static void InsertBillOfMaterialsLine(
            int billOfMaterialsLineId,
            int innerLinkExcludedFrom,
            int innerLinkExcludedTo,
            int mainLinkExcludedFrom,
            int mainLinkExcludedTo,
            int itemSequence,
            string partNumber,
            int billOfMaterialsHeaderId)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   BillOfMaterialsLineId = context.Param(billOfMaterialsLineId),
                                   InnerLinkExcludedFrom = context.Param(innerLinkExcludedFrom),
                                   InnerLinkExcludedTo = context.Param(innerLinkExcludedTo),
                                   MainLinkExcludedFrom = context.Param(mainLinkExcludedFrom),
                                   MainLinkExcludedTo = context.Param(mainLinkExcludedTo),
                                   ItemSequence = context.Param(itemSequence),
                                   PartNumber = context.Param(partNumber),
                                   BillOfMaterialsHeaderId = context.Param(billOfMaterialsHeaderId),
                               },
                p => new Q(@"INSERT  INTO BillOfMaterialsLine
        ( BillOfMaterialsLineId,
          InnerLinkExcludedFrom,
          InnerLinkExcludedTo,
          MainLinkExcludedFrom,
          MainLinkExcludedTo,
          ItemSequence,
          PartNumber,
          BillOfMaterialsHeaderId
        )
VALUES  ( ")._(p.BillOfMaterialsLineId)._(@",
          ")._(p.InnerLinkExcludedFrom)._(@",
          ")._(p.InnerLinkExcludedTo)._(@",
          ")._(p.MainLinkExcludedFrom)._(@",
          ")._(p.MainLinkExcludedTo)._(@",
          ")._(p.ItemSequence)._(@",
          ")._(p.PartNumber)._(@",
          ")._(p.BillOfMaterialsHeaderId)._(@"
        )").NonQuery());
        }

        private static void InsertModel(int modelId, string modelCode, string modelSequence)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   ModelId = context.Param(modelId),
                                   ModelCode = context.Param(modelCode),
                                   ModelSequence = context.Param(modelSequence)
                               },
                p => new Q(@"INSERT  INTO Model
        ( ModelId,
          ModelCode,
          ModelSequence
        )
VALUES  ( ")._(p.ModelId)._(@",
          ")._(p.ModelCode)._(@",
          ")._(p.ModelSequence)._(@"
        )").NonQuery());
        }

        private static void InsertLocalizedPublication(string publicationName, int localizedPublicationId, int publicationId, string languageCode)
        {
            QExecutor.ExecuteNonQuery(
                context => new {
                                   PublicationName = context.Param(publicationName),
                                   LocalizedPublicationId = context.Param(localizedPublicationId),
                                   PublicationId = context.Param(publicationId),
                                   LanguageCode = context.Param(languageCode)
                               },
                p => new Q(@"INSERT  INTO LocalizedPublication
        ( PublicationName,
          LocalizedPublicationId,
          PublicationId,
          LanguageCode
        )
VALUES  ( ")._(p.PublicationName)._(@",
          ")._(p.LocalizedPublicationId)._(@",
          ")._(p.PublicationId)._(@",
          ")._(p.LanguageCode)._(@"
        )").NonQuery());
        }
    }
}